package dao;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;

import com.healthmarketscience.jackcess.Column;
import com.healthmarketscience.jackcess.ColumnBuilder;
import com.healthmarketscience.jackcess.Cursor;
import com.healthmarketscience.jackcess.DataType;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.IndexBuilder;
import com.healthmarketscience.jackcess.Table;
import com.healthmarketscience.jackcess.TableBuilder;

import util.Debug;
/**
 * Singleton for accessing a MS Access database
 * using third party libraries from http://jackcess.sourceforge.net/
 * ( T E S T   S T A T E ! ! !   Not completely implemented!)
 * @author Jolle
 * @version 1.0.0
 */
public class AccessJacks implements DBAccessInterface {
	
	private static AccessJacks instance = null;
	
	private Database db = null;
	private String dbName = "";

	/**
	 * private constructor
	 */
	private AccessJacks() {
		
	}
	/**
	 * Return the current instance.
	 * @return instance
	 */
	public static AccessJacks getInstance() {
		if (instance == null) {
			instance = new AccessJacks();
		}
		return instance;
	}
	/**
	 * DSN is not used.
	 * @param dsn
	 */
	public void setDSN(DSN dsn) {
		// DSN is not used
	}
	/**
	 * Get the database name.
	 * @return String
	 */
	public String getDbName() {
		return this.dbName;
	}
	/**
	 * Check whether a connection exists.
	 * @return true if a connection is open
	 */
	public boolean isConnected() {
		return (this.db != null);
	}
	/**
	 * Connect to database.
	 * @throws DBException
	 */
	public void connect() throws DBException {
		connect(this.dbName);
	}
	/**
	 * Connecting a MS Access DB.
	 * @param database name
	 * @throws DBException
	 */
	public void connect(String database) throws DBException {
		Debug.print("connecting ACCESS (Jackcess): " + database);
		try {
			this.db = Database.open(new File(database));
			this.dbName = database;
			/*
			// This method can show the database password of non-encrypted databases!
			System.out.println("password: " + db.getDatabasePassword());
			System.out.println("db props: " + db.getDatabaseProperties().toString());
			System.out.println("summary:  " + db.getSummaryProperties().toString());
			System.out.println("catalog:  " + db.getSystemCatalog().toString());
			*/
		} catch (Exception e) {
			throw new DBException("Error while conecting", e);
		}
	}
	/**
	 * Execute an sql statement without return any value.
	 * @param query
	 * @throws DBException
	 */
	public void execute(String query) throws DBException {
		/*
		 *   ! ! !  T E S T A R E A  ! ! !
		 *   TODO: get command, table, fields and values from query
		 *   PROBLEM 1: Fields have a specific order!
		 *   PROBLEM 2: Values are strongly typed Java objects!
		 */
		String command = "";
		String tableName = "";
		//String fields[] = {};
		String values[] = {};
		try {
			if (command.equalsIgnoreCase("INSERT")) {
				Table table = this.db.getTable(tableName);
				table.addRow(Column.AUTO_NUMBER, values[0], values[1], values[2]);

			}
			
			
		} catch (Exception e) {
			throw new DBException("Error while executing command:\n" + query, e);
		}
	}
	/**
	 * Get a result set from a query.
	 * @param query
	 * @return ResultSet
	 * @throws DBException
	 */
	public ResultSet query(String query) throws DBException {
		/*
		 *   ! ! !  T E S T A R E A  ! ! !
		 *   TODO: get command, table, fields and values from query
		 *   PROBLEM: Need a kind of MockResultSet to fill and return!
		 */
		ResultSet rs = null;
		try {
			Column columnPattern = null;
			String searchText = "";
			String valuePattern = "";
			
			//System.out.println(this.db.getTable("countrymodel").display());
			
			Table table = this.db.getTable("countrymodel");
            Cursor cursor = Cursor.createCursor(table);
            cursor.beforeFirst();
            
            // find a specific row
            Map<String, Object> row = Cursor.findRow(table, Collections.singletonMap("a", "foo"));
            if(row != null) {
              System.out.println("Found row where 'a' == 'foo': " + row);
            } else {
              System.out.println("Could not find row where 'a' == 'foo'");
            }
            //Show me all rows with column 'town' = nyc
            while (cursor.findRow(table.getColumn("town"), "nyc")) {
                System.out.println(cursor.getCurrentRow());
                cursor.moveToNextRow();
            }
            // or
			while (cursor.moveToNextRow()) {
				if (cursor.currentRowMatches(columnPattern, valuePattern)) {
					// handle matching row here
				}
			}
			// or
			while (cursor.moveToNextRow()) {
				if (cursor.getCurrentRowValue(columnPattern).toString().contains(searchText)) {
					System.out.println(cursor.getCurrentRow());
				}
			}
			
			
		} catch (Exception e) {
			throw new DBException("Error while executing query:\n" + query, e);
		}
		return rs;
	}
	/**
	 * Statements aren't used.
	 */
	public void closeStatement() {
		// statements aren't used
	}
	/**
	 * Close database connection.
	 * @throws SQLException
	 */
	public void closeConnection() throws DBException {
		if (this.isConnected()) {
			try {
				Debug.print("close connection");
				db.close();
				db = null;
			} catch (Exception e) {
				throw new DBException("Error while closing connection", e);
			}
		}
	}
	/**
	 * Get a list of existing databases in the current connection.
	 * @return String array with database names or schemas
	 */
	public String[] getDataBasesList() throws DBException {
		ArrayList<String> dbList = new ArrayList<String>();
		try {
			// TODO: check out how it works with Access (search file system?)
		} catch (Exception e) {
			throw new DBException("Error while retrieving databases", e);
		}
		String[] keyArray = dbList.toArray(new String[dbList.size()]);
		Arrays.sort(keyArray);
		return keyArray;
	}
	/**
	 * Get a list of existing tables in the current database.
	 * @return String array with table names
	 */
	public String[] getTablesList() throws DBException {
		try {
			Object[] tablesObj = this.db.getTableNames().toArray();
			String[] tables = Arrays.copyOf(tablesObj, tablesObj.length, String[].class);
			Arrays.sort(tables);
			return tables;
		} catch (Exception e) {
			throw new DBException("Error while retrieving tables", e);
		}
	}
	/**
	 * Get a list of key/value pairs (field name/field value).
	 * @param query
	 * @return ArrayList
	 * @throws DBException
	 */
	public ArrayList<HashMap<String, Object>> getResultAsList(String query) throws DBException {
		ResultSet resultSet = this.query(query);
		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		try {
			int columns = resultSet.getMetaData().getColumnCount();
			while (resultSet.next()) {
				HashMap<String, Object> row = new HashMap<String, Object>(columns);
				for (int i = 1; i <= columns; ++i) {
					row.put(resultSet.getMetaData().getColumnName(i), resultSet.getObject(i));
				}
				list.add(row);
			}
			resultSet.close();
		} catch (Exception e) {
			throw new DBException("Error while listing results", e);
		}
		return list;
	}
	/**
	 * Create the given table.
	 * @param name of the table to create
	 * @return SQL statement as string
	 * @throws DBException
	 */
	public void createTable(String tableName) throws DBException {
		/*
		 *  Attention!
		 *  The size of all text fields is fixed to 50!
		 *  No way found to change this.
		 */
		try {
			if (tableName == "countrymodel") {
				new TableBuilder(tableName)
					.addColumn(new ColumnBuilder("id", DataType.LONG).setAutoNumber(true))
					.addColumn(new ColumnBuilder("country_id", DataType.TEXT))
					.addColumn(new ColumnBuilder("name", DataType.TEXT))
					
					.addIndex(new IndexBuilder(IndexBuilder.PRIMARY_KEY_NAME)
					.addColumns("id").setPrimaryKey())
					.addIndex(new IndexBuilder("CidIndex").addColumns("country_id"))
					.toTable(this.db);
			}
			else if (tableName == "citymodel") {
				new TableBuilder(tableName)
					.addColumn(new ColumnBuilder("id", DataType.LONG).setAutoNumber(true))
					.addColumn(new ColumnBuilder("city_id", DataType.TEXT))
					.addColumn(new ColumnBuilder("name", DataType.TEXT))
					.addColumn(new ColumnBuilder("country_id", DataType.TEXT))
					.addColumn(new ColumnBuilder("country_name", DataType.TEXT))
					.addColumn(new ColumnBuilder("lat", DataType.NUMERIC))
					.addColumn(new ColumnBuilder("lon", DataType.NUMERIC))
					
					.addIndex(new IndexBuilder(IndexBuilder.PRIMARY_KEY_NAME)
					.addColumns("id").setPrimaryKey())
					.addIndex(new IndexBuilder("CidIndex").addColumns("city_id"))
					.toTable(this.db);
			}
			else if (tableName == "weathermodel") {
				new TableBuilder(tableName)
					.addColumn(new ColumnBuilder("id", DataType.LONG).setAutoNumber(true))
					.addColumn(new ColumnBuilder("city_id", DataType.TEXT))
					.addColumn(new ColumnBuilder("info", DataType.TEXT))
					.addColumn(new ColumnBuilder("temperature", DataType.NUMERIC))
					.addColumn(new ColumnBuilder("wind_direction", DataType.TEXT))
					.addColumn(new ColumnBuilder("wind_deg", DataType.INT))
					.addColumn(new ColumnBuilder("wind_speed", DataType.NUMERIC))
					.addColumn(new ColumnBuilder("observ_time", DataType.TEXT))
					.addColumn(new ColumnBuilder("tstamp", DataType.DOUBLE))
					.addColumn(new ColumnBuilder("date_timestamp", DataType.TEXT))
					.addColumn(new ColumnBuilder("visibility", DataType.INT))
					.addColumn(new ColumnBuilder("pressure", DataType.DOUBLE))
					.addColumn(new ColumnBuilder("clouds", DataType.TEXT))
					
					.addIndex(new IndexBuilder(IndexBuilder.PRIMARY_KEY_NAME)
					.addColumns("id").setPrimaryKey())
					.addIndex(new IndexBuilder("CidIndex").addColumns("city_id"))
					.toTable(this.db);
			}
			else {
				return;
			}
		} catch (Exception e) {
			throw new DBException("Error while creating table", e);
		}
	}
	/**
	 * Check whether the given table exists in current database.
	 * @param table name to check
	 * @return true if table exists, otherwise false
	 * @throws DBException
	 */
	public boolean tableExists(String table) throws DBException {
		String[] tables = this.getTablesList();
		for (String tableName : tables) {
			if (tableName.equalsIgnoreCase(table)) {
				return true;
			}
		}
		return false;
	}
}
